# GF_PY312_CLASS_Pandas_2_SQLAlchemy_2.py
# Create by GF 2025-04-02 23:42

import pandas
import sqlalchemy

# ##################################################

class Pandas_2_SQLAlchemy_2(object):

    def __init__(self):

        self.MYSQL_DB_HOST = "null"
        self.MYSQL_DB_NAME = "dataset"
        self.MYSQL_DB_USER = "null"
        self.MYSQL_DB_PASSWORD = "null"

    def MySQL_Append_Records_from_XLSX(self, XLSX:str, Sheet_Name:str, MYSQL_DB_TABLE:str) -> int:

        SQLAlchemy_Engine = sqlalchemy.create_engine(
            "mysql+pymysql://%s:%s@%s/%s" % ( # Example: "mysql+pymysql://root:abcd1234@127.0.0.1/working"
                self.MYSQL_DB_USER, self.MYSQL_DB_PASSWORD, self.MYSQL_DB_HOST, self.MYSQL_DB_NAME
            )
        )
        # ..........................................
        XLSX_DF = pandas.read_excel(XLSX, sheet_name=Sheet_Name)
        # ..........................................
        Affected_Rows = XLSX_DF.to_sql(MYSQL_DB_TABLE, con=SQLAlchemy_Engine, index=False, if_exists='append', method='multi')
        # ..........................................
        return Affected_Rows

    def MySQL_Append_Records_from_Pandas_DataFream(self, Pandas_DataFrame:str, MYSQL_DB_TABLE:str) -> int:

        SQLAlchemy_Engine = sqlalchemy.create_engine(
            "mysql+pymysql://%s:%s@%s/%s" % ( # Example: "mysql+pymysql://root:abcd1234@127.0.0.1/working"
                self.MYSQL_DB_USER, self.MYSQL_DB_PASSWORD, self.MYSQL_DB_HOST, self.MYSQL_DB_NAME
            )
        )
        # ..........................................
        # pandas.DataFrame.to_sql(name:str, con:object, index=False, if_exists="append", method:str)
        # Args:
        # - method = None: 使用标准的 SQL INSERT 语句 (每行一个)。
        # - method = "multi": 在一个 INSERT 语句中传递多个值。
        # Returns
        # - int: Number of Affected Rows (method = None / method = "multi").
        # Others:
        # - If Pandas DataFrame is Empty in (if_exists="append") Mode, return 0.
        Affected_Rows = Pandas_DataFrame.to_sql(MYSQL_DB_TABLE, con=SQLAlchemy_Engine, index=False, if_exists='append', method='multi')
        # ..........................................
        return Affected_Rows

    def MySQL_Query(self, SQL_Statment:str):

        SQLAlchemy_Engine = sqlalchemy.create_engine(
            "mysql+pymysql://%s:%s@%s/%s" % ( # Example: "mysql+pymysql://root:abcd1234@127.0.0.1/working"
                self.MYSQL_DB_USER, self.MYSQL_DB_PASSWORD, self.MYSQL_DB_HOST, self.MYSQL_DB_NAME
            )
        )
        # ..........................................
        Result_DF = pandas.read_sql_query(SQL_Statment, con=SQLAlchemy_Engine)
        # ..........................................
        return Result_DF

# EOF Signed by GF.
